using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Common;
using Smo = Microsoft.SqlServer.Management.Smo;
using System.Collections.Generic;
/// <summary>
/// Helper class to make it easier to request objects or collections from the 
/// SQL Server 
/// </summary>
namespace SqlWebAdmin.CSharp
{
    public sealed class SmoHelper
    {
        // make constructor private to keep the class from being instantiated
        private SmoHelper()
        {
        }
		public static Microsoft.SqlServer.Management.Smo.Server CurrentServer
		{
			get
			{
				HttpContext context = HttpContext.Current;
				return context.Cache[String.Format("{0}:CurrentServer", context.Session.SessionID)] as Smo.Server;
			}
			set
			{
				HttpContext context = HttpContext.Current;
				//context.Cache[String.Format("{0}:CurrentServer", context.Session.SessionID)] = value;
				context.Cache.Insert(String.Format("{0}:CurrentServer", context.Session.SessionID), value);
			}
		}

		public static void ResetServer(ref Smo.Server smoServer)
		{
			HttpContext.Current.Trace.Write("SmoHelper", "Resetting SMO Server");
			ServerConnection currentConnection = smoServer.ConnectionContext;
			smoServer = new Microsoft.SqlServer.Management.Smo.Server(currentConnection);
	 
		}

        public static string GetConnectionString()
        {
            string returnValue = null;
            HttpContext context = HttpContext.Current;
            context.Trace.Write("SmoHelper", "End GetConnectionString");
            if (context != null)
            {
                string cryptConnString = (string)context.Profile["ConnectionString"];

                if (cryptConnString == null || cryptConnString.Length < 1)
                    context.Response.Redirect(SiteUrls.Credentials());
                string clearConnString = string.Empty;

                try
                {
                    Encryption crypt = Encryption.Current();
                    returnValue = crypt.Decrypt(cryptConnString);
                }
                catch (Exception ex)
                {
                    HttpContext.Current.Response.Redirect(String.Format("~/credentials.aspx?Error={0}&RU={1}", context.Server.UrlEncode(ex.Message), context.Request["SCRIPT_NAME"]));
                }
            }

            context.Trace.Write("SmoHelper", "End GetConnectionString");
            return returnValue;
        }

        /// <summary>
        /// Method to return an Smo.Server instance based on information in the Session. If the expected
        /// session key is not in the session this will cause an exception to be thrown. Use the <see>GetServer</see>
        /// method that allows you to pass in the server name as a parameter to the method.
        /// </summary>
        /// <returns></returns>
        public static Microsoft.SqlServer.Management.Smo.Server GetServer()
		{
				Smo.Server server = null;
				HttpContext context = HttpContext.Current;
				if (context != null)
				{
                    context.Trace.Write("SmoHelper", "Begin GetServer");
                    if (CurrentServer == null)
					{
                        string clearConnString = GetConnectionString();
						server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(new SqlConnection(clearConnString)));
						try
						{
							// get a build number from ther server, this will ensure that we have connected with the server.  
							int buildNumber = server.ConnectionContext.ServerVersion.BuildNumber;
						}
						catch (ConnectionFailureException sqlex)
						{
							//HttpContext context = HttpContext.Current;
							context.Response.Redirect(String.Format("~/credentials.aspx?Error={0}&RU={1}", context.Server.UrlEncode(sqlex.Message), context.Request["SCRIPT_NAME"]));
							//	context.Response.End();
						}

						CurrentServer = server;
					}
					else
					{
						context.Trace.Write("Cache Hit", "Extracting Connection from user Cache");

						server = CurrentServer;
						if (server.ConnectionContext.IsOpen == false)
							server.ConnectionContext.Connect();

						// The cache here is really a connection cache.  We won't have to pay the price of retrieving and decrypting 
						// the connection string from the profile data store.
						ResetServer(ref server);

					}

                    context.Trace.Write("SmoHelper", "Begin GetServer");			
					return server;
				}
				else
					throw new System.InvalidOperationException("Trying to access an unspecified server");
			
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="serverName"></param>
        /// <returns></returns>
        public static Microsoft.SqlServer.Management.Smo.Server GetServer(string serverName, bool useIntegratedSecurity, string username, string password)
        {
            Smo.Server smoServer = new Microsoft.SqlServer.Management.Smo.Server(serverName);
            if (useIntegratedSecurity)
            {
                smoServer.ConnectionContext.LoginSecure = false;
                smoServer.ConnectionContext.Login = username;
                smoServer.ConnectionContext.Password = password;
            }
            return smoServer;
        }
        public static Smo.DatabaseCollection GetDatabases()
        {
            Smo.Server server = GetServer();
            if (server != null)
                return server.Databases;
            else
                return null;
        }

        public static Smo.Database GetDatabase()
        {
            HttpContext context = HttpContext.Current;
            if (context != null)
            {
                string databaseName = context.Request.QueryString[QueryStringKeys.Database];
                if (databaseName != null)
                {
                    Smo.DatabaseCollection databases = GetDatabases();
                    if (databases != null)
                    {
                        if (databases.Contains(databaseName))
                            return databases[databaseName];
                        else
                            throw new Exception(String.Format("Database ({0}) not found.", databaseName));
                    }
                    else
                        throw new InvalidOperationException("No databases found for the server");
                }
                else
                    throw new InvalidOperationException("Could not retrieve a database object without be instructed as to which specific database to access");
            }
            else
                throw new InvalidOperationException("Could not retrieve a database object without be instructed as to which specific database to access");
        }

        public static Smo.TableCollection GetTables()
        {
            HttpContext context = HttpContext.Current;
            Smo.Database db = GetDatabase();
            if (db != null)
            {
                return db.Tables;
            }
            return null;
        }

        public static Smo.Table GetTable()
        {
            HttpContext context = HttpContext.Current;
            context.Trace.Write("SmoHelper", "Begin GetTable");
            Smo.Database db = GetDatabase();
            Smo.Table returnValue = null;
            if (db != null)
            {
                string tableName = context.Request.QueryString[QueryStringKeys.Table];
                string schemaName = context.Request.QueryString[QueryStringKeys.Schema];

                if (String.IsNullOrEmpty(tableName))
                    throw new ArgumentNullException(QueryStringKeys.Table, "Table name was not specified");

                if (db.Tables.Contains(tableName, schemaName))
                {
                    returnValue = db.Tables[tableName, schemaName];
                }
            }

            context.Trace.Write("SmoHelper", "End GetTable");
            // fall through
            return null;
        }

        public static Smo.StoredProcedureCollection GetStoredProcedures()
        {
            HttpContext context = HttpContext.Current;
            Smo.Database db = GetDatabase();
            if (db != null)
            {
                return db.StoredProcedures;
            }
            return null;
        }

        public static Smo.StoredProcedure GetStoredProcedure()
        {
            HttpContext context = HttpContext.Current;
            Smo.Database db = GetDatabase();
            if (db != null)
            {
                string procedureName = context.Request.QueryString[QueryStringKeys.StoredProcedure];
                string schemaName = context.Request.QueryString[QueryStringKeys.Schema];

                if (String.IsNullOrEmpty(procedureName))
                    throw new ArgumentNullException(QueryStringKeys.StoredProcedure, "Stored Procedure name was not specified");

                if (db.StoredProcedures.Contains(procedureName, schemaName))
                {
                    return db.StoredProcedures[procedureName, schemaName];
                }
            }

            // fall through
            return null;
        }

		public static List<Smo.Database> FilterSystemObjects(Smo.DatabaseCollection dbc, bool hideUserObjects)
		{
			List<Smo.Database> dbCollection = new List<Microsoft.SqlServer.Management.Smo.Database>();

			for (int i = 0; i < dbc.Count; i++)
			{
				if (dbc[i].IsSystemObject == hideUserObjects)
					dbCollection.Add(dbc[i]);
			}

			return dbCollection;
		}

		public static List<Smo.Table> FilterSystemObjects(Smo.TableCollection tbc, bool hideUserObjects)
		{
			List<Smo.Table> tbCollection = new List<Microsoft.SqlServer.Management.Smo.Table>();

			for (int i = 0; i < tbc.Count; i++)
			{
				if (tbc[i].IsSystemObject == hideUserObjects)
					tbCollection.Add(tbc[i]);
			}

			return tbCollection;
		}

		public static List<Smo.StoredProcedure> FilterSystemObjects(Smo.StoredProcedureCollection spc, bool hideUserObjects)
		{
			List<Smo.StoredProcedure> spCollection = new List<Microsoft.SqlServer.Management.Smo.StoredProcedure>();

			for (int i = 0; i < spc.Count; i++)
			{
				if (spc[i].IsSystemObject == hideUserObjects)
					spCollection.Add(spc[i]);
			}

			return spCollection;
		}

		public static List<Smo.Database> ListUserDatabases(Smo.DatabaseCollection dbc, bool hideUserObjects)
		{
            HttpContext context = HttpContext.Current;
            context.Trace.Write("SmoHelper", "Start ListUserDatabases");

			List<Smo.Database> dbCollection = new List<Microsoft.SqlServer.Management.Smo.Database>();

            DateTime start = DateTime.Now;
            foreach (Smo.Database db in dbc)
            {
                if (db.IsAccessible)
                    dbCollection.Add(db);
            }

            //for (int i = 0; i < dbc.Count; i++)
            //{
            //    try
            //    {
            //        int cnt = dbc[i].Tables.Count; // force it to check to see if we have access to table.
            //        if(dbc[i].IsSystemObject == hideUserObjects)
            //            dbCollection.Add(dbc[i]);
            //    }
            //    catch(Exception ex)
            //    {
            //        HttpContext.Current.Trace.Warn("Database Access Exception",String.Format("Exception: {0}", ex.Message));
            //    }
            //}

            DateTime end = DateTime.Now;


            context.Trace.Write("SmoHelper", "End ListUserDatabases");
            return dbCollection;
        }
	}
}